MySQL stored procedures

Must Watch!



MustWatch





Summary: MySQL stored procedures, their advantages, and disadvantages.


Getting started with stored procedures

The following SELECT statement returns all rows in the table customers from the sample database:

SELECT 
    customerName, 
    city, 
    state, 
    postalCode, 
    country
FROM
    customers
ORDER BY customerName;
This picture shows the partial output of the query:



When you use MySQL Workbench or mysql shell to issue the query to MySQL Server, MySQL processes the query and returns the result set.

If you want to save this query on the database server for execution later, one way to do it is to use a stored procedure.

The following CREATE PROCEDURE statement creates a new stored procedure that wraps the query above:

DELIMITER $$

CREATE PROCEDURE GetCustomers()
BEGIN
	SELECT 
		customerName, 
		city, 
		state, 
		postalCode, 
		country
	FROM
		customers
	ORDER BY customerName;    
END$$
DELIMITER ;

By definition, a stored procedure is a segment of declarative SQL statements stored inside the MySQL Server. 
In this example, we have just created a stored procedure with the name GetCustomers().

Once you save the stored procedure, you can invoke it by using the CALL statement:

CALL GetCustomers();
And the statement returns the same result as the query.

The first time you invoke a stored procedure, MySQL looks up for the name in the database catalog, compiles the stored procedure’s code, place it in a memory area known as a cache, and execute the stored procedure.

If you invoke the same stored procedure in the same session again, MySQL just executes the stored procedure from the cache without having to recompile it.

A stored procedure can have parameters so you can pass values to it and get the result back. 
For example, you can have a stored procedure that returns customers by country and city. 
In this case, the country and city are parameters of the stored procedure.

A stored procedure may contain control flow statements such as IF, CASE, and LOOP that allow you to implement the code in the procedural way.

A stored procedure can call other stored procedures or stored functions, which allows you to modulize your code.

Note that you will learn step by step how to create a new stored procedure in the next tutorial.


stored procedures advantages

The following are the advantages of stored procedures.


Reduce network traffic

Stored procedures help reduce the network traffic between applications and MySQL Server. 
Because instead of sending multiple lengthy SQL statements, applications have to send only the name and parameters of stored procedures.


Centralize business logic in the database

You can use the stored procedures to implement business logic that is reusable by multiple applications. 
The stored procedures help reduce the efforts of duplicating the same logic in many applications and make your database more consistent.


Make database more secure

The database administrator can grant appropriate privileges to applications that only access specific stored procedures without giving any privileges on the underlying tables.


stored procedures disadvantages

Besides those advantages, stored procedures also have disadvantages:


Resource usages

If you use many stored procedures, the memory usage of every connection will increase substantially.

Besides, overusing a large number of logical operations in the stored procedures will increase the CPU usage because the MySQL is not well-designed for logical operations.


Troubleshooting

It’s difficult to debug stored procedures. 
Unfortunately, MySQL does not provide any facilities to debug stored procedures like other enterprise database products such as Oracle and SQL Server.


Maintenances

Developing and maintaining stored procedures often requires a specialized skill set that not all application developers possess. 
This may lead to problems in both application development and maintenance.

In this tutorial, you have learned about the MySQL stored procedures, their advantages, and disadvantages. 
Let’s move on to learn how to create a new stored procedure in MySQL.


Summary: how to change the default MySQL delimiter by using the DELIMITER command.

When you write SQL statements, you use the semicolon (;) to separate two statements like the following example:

SELECT * FROM products;

SELECT * FROM customers;
A MySQL client program such as MySQL Workbench or mysql program uses the (;) delimiter to separate statements and executes each statement separately.

A stored procedure, however, consists of multiple statements separated by a semicolon (;).

If you use a MySQL client program to define a stored procedure that contains semicolon characters, the MySQL client program will not treat the whole stored procedure as a single statement, but many statements.

Therefore, you must redefine the delimiter temporarily so that you can pass the whole stored procedure to the server as a single statement.

To redefine the default delimiter, you use the DELIMITER command:

DELIMITER delimiter_character
The delimiter_character may consist of a single character or multiple characters e.g., // or $$. 
However, you should avoid using the backslash (\) because this is the escape character in MySQL.

For example, this statement changes the delimiter to //:

DELIMITER //
Once change the delimiter, you can use the new delimiter to end a statement as follows:

DELIMITER //

SELECT * FROM customers //

SELECT * FROM products //
To change the delimiter back to semicolon, you use this statement:

DELIMITER ;

Using MySQL DELIMITER for stored procedures

A stored procedure typically contains multiple statements separated by semicolon (;).
 To use compile the whole stored procedure as a single compound statement, you need to temporarily change the delimiter from the semicolon (;) to anther delimiters such as $$ or //:

DELIMITER $$

CREATE PROCEDURE sp_name()
BEGIN
  -- statements
END $$

DELIMITER ;
Note that you will learn the syntax of creating a stored procedure in the next tutorial.

In this code:


In this tutorial, you have learned how to use the MySQL DELIMITER command to change the default delimiter (;) to another.


Summary: step by step how to the MySQL CREATE PROCEDURE statement to create new stored procedures.



CREATE PROCEDURE statement

This query returns all products in the products table from the sample database.

SELECT * FROM products;
The following statement creates a new stored procedure that wraps the query:

DELIMITER //

CREATE PROCEDURE GetAllProducts()
BEGIN
	SELECT *  FROM products;
END //

DELIMITER ;
To execute these statements:

First, launch MySQL Workbench.

Second, create a new SQL tab for executing queries:



Third, enter the statements in the SQL tab:



Fouth, execute the statements. 
Note that you can select all statements in the SQL tab (or nothing) and click the Execute button. 
If everything is fine, MySQL will create the stored procedure and save it in the server.



Fifth, check the stored procedure by opening the Stored Procedures node. 
If you don’t see the stored procedure, you can click the Refresh button next to the SCHEMAS title:



Congratulation! you have successfully created the first stored procedure in MySQL.

Let’s examine the syntax of the stored procedure.

The first and last DELIMITER commands are not a part of the stored procedure. 
The first DELIMITER command changes the default delimiter to // and the last DELIMITER command changes the delimiter back to the default one which is semicolon (;).

To create a new stored procedure, you use the CREATE PROCEDURE statement.

Here is the basic syntax of the CREATE PROCEDURE statement:

CREATE PROCEDURE procedure_name(parameter_list)
BEGIN
   statements;
END //
In this syntax



Executing a stored procedure

To execute a stored procedure, you use the CALL statement:

CALL stored_procedure_name(argument_list);
In this syntax, you specify the name of the stored procedure after the CALL keyword. 
If the stored procedure has parameters, you need to pass arguments inside parentheses following the stored procedure name.

This example illustrates how to call the GetAllProducts() stored procedure:

CALL GetAllProducts();
Executing this statement is the same as executing an SQL statement:



Here is the partial output:




Creating a stored procedure using the MySQL Workbench wizard

By using the MySQL Workbench wizard, you don’t have to take are of many things like delimiters or executing the command to create stored procedures.

First, right-click on the Stored Procedures from
the Navigator and select the Create Stored Procedure…
menu item.



The following tab will open:



Second, change the stored procedure’s name and add the code between the BEGIN END block:



The stored procedure name is GetAllCustomers() which returns all rows in the customers table from the sample database.

Third, Click the Apply button, MySQL Workbench will open a new window for reviewing SQL script before applying it on the database:



Fourth, Click the Apply button to confirm. 
MySQL Workbench will create the stored procedure:



Fifth, click the Finish button to close the window.

Finally, view the stored procedure in the Stored Procedures list:



In this tutorial, you have learned how to use the MySQL CREATE PROCEDURE statement to create new stored procedures in the database.


Summary: how to use the MySQL DROP PROCEDURE statement to drop a stored procedure.


Introduction to the MySQL DROP PROCEDURE statement

The DROP PROCEDURE deletes a stored procedure from the database.

The following shows the syntax of the DROP PROCEDURE statement:

DROP PROCEDURE [IF EXISTS] stored_procedure_name;

In this syntax:


When you drop a procedure that does not exist without using the IF EXISTS option, MySQL issues an error. 
In this case, if you use the IF EXISTS option, MySQL issues a warning instead.

Note that you must have the ALTER ROUTINE privilege for the stored procedure to remove it.


DROP PROCEDURE examples

Let’s take some examples of using the DROP PROCEDURE statement.


1) Using MySQL DROP PROCEDURE example

First, create a new stored procedure that returns employee and office information:

DELIMITER $$

CREATE PROCEDURE GetEmployees()
BEGIN
    SELECT 
        firstName, 
        lastName, 
        city, 
        state, 
        country
    FROM employees
    INNER JOIN offices using (officeCode);
    
END$$

DELIMITER ;

Second, use the DROP PROCEDURE to delete the GetEmployees() stored procedure:

DROP PROCEDURE GetEmployees;


2) Using MySQL DROP PROCEDURE with IF EXISTS example

The following example drops a stored procedure that does not exist:

DROP PROCEDURE abc;

MySQL issued the following error:

Error Code: 1305. 
PROCEDURE classicmodels.abc does not exist

This statement drops the same non-existing stored procedure, but with IF EXISTS option:

DROP PROCEDURE IF EXISTS abc;

This time, MySQL issued a warning.

0 row(s) affected, 1 warning(s): 1305 PROCEDURE classicmodels.abc does not exist

The statement SHOW WARNINGS shows the warning:

SHOW WARNINGS;

Here is the output:




Dropping a stored procedure using MySQL Workbench

This statement creates a new stored procedure named GetPayments():

DELIMITER $$

CREATE PROCEDURE GetPayments()
BEGIN
    SELECT 
        customerName, 
        checkNumber, 
        paymentDate, 
        amount
    FROM payments
    INNER JOIN customers 
        using (customerNumber);
END$$

DELIMITER ;

To drop the stored procedure using MySQL Workbench, you follow these steps:

First, right-click the name of the stored procedure that you want to remove and choose Drop Stored Procedure… option.



MySQL Workbench will display a confirmation window.

Second, click Review SQL to review the SQL statement that MySQL Workbench will apply to the database or Drop Now if you want to remove the stored procedure immediately.



Third, review the SQL code to be executed and click the Execute button to drop the stored procedure.



In this tutorial, you have learned how to use the MySQL DROP PROCEDURE statement to drop a stored procedure.


Summary: how to alter an existing stored procedure in the database using MySQL Workbench.

Sometimes, you may want to alter a stored procedure by adding or removing parameters or even changing its body.

Fortunately, MySQL does not have any statement that allows you to directly modify the parameters and body of the stored procedure.

To make such changes, you must drop ad re-create the stored procedure using the DROP PROCEDURE and CREATE PROCEDURE statements.


Altering a stored procedure using MySQL Workbench

MySQL Workbench provides you with a good tool that allows you to change a stored procedure quickly.

First, create a stored procedure that returns the total amount of all sales orders:

DELIMITER $$

CREATE PROCEDURE GetOrderAmount()
BEGIN
    SELECT 
        SUM(quantityOrdered * priceEach) 
    FROM orderDetails;
END$$

DELIMITER ;

Suppose that you want to get the total amount by a given sales order. 
So you need to add a parameter and change the code in the stored procedure.

Second, right-click the stored procedure that you want to change and select Alter Stored Procedure…



MySQL Workbench will open a new tab that contains the definition of the stored procedure.

Third, make the changes and click the Apply button.



MySQL Workbench will display a SQL Script review window.



As you can see, it uses a sequence of DROP PROCEDURE and CREATE PROCEDURE statements to carry the modification.

Fourth, click the Apply button to execute the script.

MySQL Workbench will display a window that shows the status of the script execution.



Finally, click the Finish button to complete the change.

In this tutorial, you have learned how to alter a stored procedure using MySQL Workbench.


Summary: how to list stored procedures from databases in a MySQL Server.


Listing stored procedures using SHOW PROCEDURE STATUS statement

Here is the basic syntax of the SHOW PROCEDURE STATUS statement:

SHOW PROCEDURE STATUS [LIKE 'pattern' | WHERE search_condition]
The SHOW PROCEDURE STATUS statement shows all characteristic of stored procedures including stored procedure names. 
It returns stored procedures that you have a privilege to access.

The following statement shows all stored procedure in the current MySQL server:

SHOW PROCEDURE STATUS;
Here is the partial output:



If you just want to show stored procedures in a particular database, you can use a WHERE clause in the
 SHOW PROCEDURE STATUS as shown in the following statement:

SHOW PROCEDURE STATUS WHERE search_condition;
For example, this statement lists all stored procedures in the sample database classicmodels:

SHOW PROCEDURE STATUS WHERE db = 'classicmodels';
In case you want to find stored procedures whose names contain a specific word, you can use the LIKE clause as follows:

SHOW PROCEDURE STATUS LIKE '%pattern%'
The following statement shows all stored procedure whose names contain the wordOrder:

SHOW PROCEDURE STATUS LIKE '%Order%'



Listing stored procedures using the data dictionary

The routines table in the information_schema database contains all information on the stored procedures and stored functions of all databases in the current MySQL server.

To show all stored procedures of a particular database, you use the following query:

SELECT 
    routine_name
FROM
    information_schema.routines
WHERE
    routine_type = 'PROCEDURE'
        AND routine_schema = '<database_name>';
For example, this statement lists all stored procedures in the classicmodels database:

SELECT 
    routine_name
FROM
    information_schema.routines
WHERE
    routine_type = 'PROCEDURE'
        AND routine_schema = 'classicmodels';



Showing stored procedures using MySQL Workbench

In MySQL Workbench, you can view all stored procedures from a database.

Step 1. 
Access the database that you want to view the stored procedures.

Step 2. 
Open the Stored Procedures menu. 
You will see a list of stored procedures that belong to the current database.



In this tutorial, you have learned how to list the stored procedures in a database by querying them from the data dictionary.


Summary: variables in the stored procedure, how to declare, and use variables. 
In addition, you will learn about the scopes of variables.

A variable is a named data object whose value can change during the stored procedure execution. 
You typically use variables in stored procedures to hold immediate results. 
These variables are local to the stored procedure.

Before using a variable, you must declare it.


Declaring variables

To declare a variable inside a stored procedure, you use the DECLARE 
statement as follows:

DECLARE variable_name datatype(size) [DEFAULT default_value];
In this syntax:


The following example declares a variable named totalSale with the data type DEC(10,2) and default value 0.0 
as follows:

DECLARE totalSale DEC(10,2) DEFAULT 0.0;
MySQL allows you to declare two or more variables that share the same data type using a single DECLARE statement. 
The following example declares two integer variables
 x and
 y, and set their default values to zero.

DECLARE x, y INT DEFAULT 0;

Assigning variables

Once a variable is declared, it is ready to use. 
To assign a variable a value, you use the SET statement:

SET variable_name = value;
For example:

DECLARE total INT DEFAULT 0;
SET total = 10;
The value of the total variable is 10 
after the assignment.

In addition to the SET
statement, you can use the
SELECT INTO statement to assign the result of a query to a variable as shown in the following example:

DECLARE productCount INT DEFAULT 0;

SELECT COUNT(*) 
INTO productCount
FROM products;
In this example:



Variable scopes

A variable has its own scope that defines its lifetime. 
If you declare a variable inside a stored procedure, it will be out of scope when the END statement of stored procedure reaches.

When you declare a variable inside the block BEGIN END, it will be out of scope if the END is reached.

MySQL allows you to declare two or more variables that share the same name in different scopes. 
Because a variable is only effective in its scope. 
However, declaring variables with the same name in different scopes is not good programming practice.

A variable whose name begins with the @ sign is a session variable. 
It is available and accessible until the session ends.


Putting it all together

The following example illustrates how to declare and use a variable in a stored procedure:

DELIMITER $$

CREATE PROCEDURE GetTotalOrder()
BEGIN
	DECLARE totalOrder INT DEFAULT 0;
    
    SELECT COUNT(*) 
    INTO totalOrder
    FROM orders;
    
    SELECT totalOrder;
END$$

DELIMITER ;

How it works.

First, declare a variable totalOrder with a default value of zero. 
This variable will hold the number of orders from the orders table.

DECLARE totalOrder INT DEFAULT 0;
Second, use the SELECT INTO
 statement to assign the variable totalOrder the number of orders selected from the orders table:

SELECT COUNT(*) 
INTO totalOrder 
FROM orders;
Third, select the value of the
variable totalOrder.

SELECT totalOrder;
Note that you will learn how to use variables practically in the subsequent tutorials. 
The example in this tutorial is just an illustration so that you understand the concept.

This statement calls the stored procedure GetTotalOrder():

CALL GetTotalOrder();
Here is the output:



In this tutorial, you have learned how you how to declare and use variables inside stored procedures.


Summary: how to create stored procedures with parameters including IN, OUT, and INTOUT parameters.


Introduction to MySQL stored procedure parameters

Almost stored procedures that you develop require parameters. 
The parameters make the stored procedure more flexible and useful.

In MySQL, a parameter has one of three modes: IN,OUT,
or INOUT.



IN parameters

IN is the default mode. 
When you define an IN parameter in a stored procedure, the calling program has to pass an argument to the stored procedure. 
In addition, the value of an IN parameter is protected. 
It means that even the value of the IN parameter is changed inside the stored procedure, its original value is retained after the stored procedure ends. 
In other words, the stored procedure only works on the copy of the IN parameter.



OUT parameters

The value of an OUT parameter can be changed inside the stored procedure and its new value is passed back to the calling program. 
Notice that the stored procedure cannot access the initial value of the OUT parameter when it starts.



INOUT parameters

An INOUT 
parameter is a combination of IN
 and OUT 
parameters. 
It means that the calling program may pass the argument, and the stored procedure can modify the INOUT parameter, and pass the new value back to the calling program.


Defining a parameter

Here is the basic syntax of defining a parameter in stored procedures:

[IN | OUT | INOUT] parameter_name datatype[(length)]
In this syntax,



stored procedure parameter examples

Let’s take some examples of using stored procedure parameters.


The IN parameter example

The following example creates a stored procedure that finds all offices that locate in a country specified by the input parameter countryName:

DELIMITER //

CREATE PROCEDURE GetOfficeByCountry(
	IN countryName VARCHAR(255)
)
BEGIN
	SELECT * 
 	FROM offices
	WHERE country = countryName;
END //

DELIMITER ;
In this example, the countryName is the IN parameter of the stored procedure.

Suppose that you want to find offices locating in the USA, you need to pass an argument (USA) to the stored procedure as shown in the following query:

CALL GetOfficeByCountry('USA');


To find offices in France, you pass the literal string France to the GetOfficeByCountry
stored procedure as follows:

CALL GetOfficeByCountry('France')


Because the countryName is the IN parameter, you must pass an argument. 
Fail to do so will result in an error:

CALL GetOfficeByCountry();
Here is the error:

Error Code: 1318. 
Incorrect number of arguments for PROCEDURE classicmodels.GetOfficeByCountry; expected 1, got 0


The OUT parameter example

The following stored procedure returns the number of orders by order status.

DELIMITER $$

CREATE PROCEDURE GetOrderCountByStatus (
	IN  orderStatus VARCHAR(25),
	OUT total INT
)
BEGIN
	SELECT COUNT(orderNumber)
	INTO total
	FROM orders
	WHERE status = orderStatus;
END$$

DELIMITER ;
The stored procedure GetOrderCountByStatus()
has two parameters:


To find the number of orders that already shipped, you call GetOrderCountByStatus
 and pass the order status as of Shipped, and also pass a session variable ( @total ) to receive the return value.

CALL GetOrderCountByStatus('Shipped',@total);
SELECT @total;


To get the number of orders that are in-process, you call the stored procedure GetOrderCountByStatus
as follows:

CALL GetOrderCountByStatus('in process',@total);
SELECT @total AS  total_in_process;



The INOUT parameter example

The following example demonstrates how to use an
INOUT parameter in the stored procedure.

DELIMITER $$

CREATE PROCEDURE SetCounter(
	INOUT counter INT,
    IN inc INT
)
BEGIN
	SET counter = counter + inc;
END$$

DELIMITER ;
In this example, the stored procedure SetCounter()
 accepts one INOUT 
parameter ( counter ) and one IN parameter ( inc ). 
It increases the counter ( counter ) by the value of specified by the inc parameter.

These statements illustrate how to call the SetSounter 
stored procedure:

SET @counter = 1;
CALL SetCounter(@counter,1); -- 2
CALL SetCounter(@counter,1); -- 3
CALL SetCounter(@counter,5); -- 8
SELECT @counter; -- 8
Here is the output:



In this tutorial, you have learned how create stored procedures with parameters including IN, OUT, and INOUT parameters.


Summary: how to use MySQL IF statement to execute a block of SQL code based on a specified condition.

Note that MySQL has an IF() function that is different from the IF statement described in this tutorial.

The IF statement has three forms: simple IF-THEN
statement, IF-THEN-ELSE statement, and IF-THEN-ELSEIF- ELSE statement.


simple IF-THEN statement

The IF-THEN statement allows you to execute a set of SQL statements based on a specified condition. 
The following illustrates the syntax of the IF-THEN statement:

IF condition THEN 
   statements;
END IF;
In this syntax:


We’ll use the customers table from the sample database for the demonstration:



See the following GetCustomerLevel() stored procedure.

DELIMITER $$

CREATE PROCEDURE GetCustomerLevel(
    IN  pCustomerNumber INT, 
    OUT pCustomerLevel  VARCHAR(20))
BEGIN
    DECLARE credit DECIMAL(10,2) DEFAULT 0;

    SELECT creditLimit 
    INTO credit
    FROM customers
    WHERE customerNumber = pCustomerNumber;

    IF credit > 50000 THEN
        SET pCustomerLevel = 'PLATINUM';
    END IF;
END$$

DELIMITER ;
The stored procedure GetCustomerLevel() accepts two parameters: pCustomerNumber and pCustomerLevel.


This statement finds all customers that have a credit limit greater than 50,000:

SELECT 
    customerNumber, 
    creditLimit
FROM 
    customers
WHERE 
    creditLimit > 50000
ORDER BY 
    creditLimit DESC;
Here is the partial output:



These statements call the GetCustomerLevel() stored procedure for customer 141 and show the value of the OUT parameter pCustomerLevel:

CALL GetCustomerLevel(141, @level);
SELECT @level;


Because the customer 141 has a credit limit greater than 50,000, its level is set to PLATINUM as expected.


IF-THEN-ELSE statement

In case you want to execute other statements when the condition in the IF branch does not evaluate to TRUE, you can use the IF-THEN-ELSE statement as follows:

IF condition THEN
   statements;
ELSE
   else-statements;
END IF;
In this syntax, if the condition evaluates to TRUE, the statements between IF-THEN and ELSE execute. 
Otherwise, the else-statements between the ELSE and END IF execute.

Let’s modify the GetCustomerLevel() stored procedure.

First, drop the GetCustomerLevel() stored procedure:

DROP PROCEDURE GetCustomerLevel;
Then, create the GetCustomerLevel() stored procedure with the new code:

DELIMITER $$

CREATE PROCEDURE GetCustomerLevel(
    IN  pCustomerNumber INT, 
    OUT pCustomerLevel  VARCHAR(20))
BEGIN
    DECLARE credit DECIMAL DEFAULT 0;

    SELECT creditLimit 
    INTO credit
    FROM customers
    WHERE customerNumber = pCustomerNumber;

    IF credit > 50000 THEN
        SET pCustomerLevel = 'PLATINUM';
    ELSE
        SET pCustomerLevel = 'NOT PLATINUM';
    END IF;
END$$

DELIMITER ;

In this new stored procedure, we include the ELSE branch. 
If the credit is not greater than 50,000, we set the customer level to NOT PLATINUM in the block between ELSE and END IF.

This query finds customers that have credit limit less than or equal 50,000:

SELECT 
    customerNumber, 
    creditLimit
FROM 
    customers
WHERE 
    creditLimit <= 50000
ORDER BY 
    creditLimit DESC;
This picture shows the partial output:



The following statements call the stored procedure for customer number 447 
and show the value of the OUT parameter pCustomerLevel:

CALL GetCustomerLevel(447, @level);
SELECT @level;


The credit limit of the customer 447 is less than 50,000, therefore, the statement in the ELSE branch executes and sets the value of the OUT parameter pCustomerLevel to NOT PLATINUM.


IF-THEN-ELSEIF-ELSE statement

If you want to execute statements conditionally based on multiple conditions, you use the following IF-THEN-ELSEIF-ELSE statement:

IF condition THEN
   statements;
ELSEIF elseif-condition THEN
   elseif-statements;
...
ELSE
   else-statements;
END IF;
In this syntax, if the condition evaluates to TRUE ,
the statements in the IF-THEN branch executes; otherwise, the next elseif-condition is evaluated.

If the elseif-condition evaluates to TRUE, the elseif-statement executes; otherwise, the next elseif-condition is evaluated.

The IF-THEN-ELSEIF-ELSE statement can have multiple ELSEIF branches.

If no condition in the IF and ELSE IF evaluates to TRUE, the else-statements in the ELSE branch will execute.

We will modify the GetCustomerLevel()
stored procedure to use the IF-THEN-ELSEIF-ELSE statement.

First, drop the GetCustomerLevel() stored procedure:

DROP PROCEDURE GetCustomerLevel;

Then, create the new GetCustomerLevel()
stored procedure that uses the the IF-THEN-ELSEIF-ELSE statement.

DELIMITER $$

CREATE PROCEDURE GetCustomerLevel(
    IN  pCustomerNumber INT, 
    OUT pCustomerLevel  VARCHAR(20))
BEGIN
    DECLARE credit DECIMAL DEFAULT 0;

    SELECT creditLimit 
    INTO credit
    FROM customers
    WHERE customerNumber = pCustomerNumber;

    IF credit > 50000 THEN
        SET pCustomerLevel = 'PLATINUM';
    ELSEIF credit <= 50000 AND credit > 10000 THEN
        SET pCustomerLevel = 'GOLD';
    ELSE
        SET pCustomerLevel = 'SILVER';
    END IF;
END $$

DELIMITER ;

In this stored procedure:


These statements call the stored procedure GetCustomerLevel() and show the level of the customer 447:

CALL GetCustomerLevel(447, @level); 
SELECT @level;


If you test the stored procedure with the customer that has a credit limit of 10000 or less, you will get the output as SILVER.

In this tutorial, you have learned how to use MySQL IF statement to conditionally execute a block of code based on specified conditions.


Summary:
how to use
MySQL CASE statements to construct complex conditional statements inside stored procedures.

Besides the IF statement, MySQL provides an
alternative
conditional
statement called the CASE statement for constructing conditional statements in stored procedures. 
The CASE statements make the code more readable and efficient.

The CASE statement has two forms: simpleCASE and searched CASE statements.

Note that if you want to add the if-else logic to an SQL statement, you use the CASE expression which is different from the CASE statement described in this tutorial.


Simple CASE statement

The following is the basic syntax of the simple CASE statement:

CASE case_value
   WHEN when_value1 THEN statements
   WHEN when_value2 THEN statements
   ...
   [ELSE else-statements]
END CASE;
In this syntax, the simple CASE statement sequentially compares the case_value is with the when_value1, when_value2, … until it finds one is equal. 
When the CASE finds a case_value equal to a when_value, it executes statements in the corresponding THEN clause.

If CASE cannot find any when_value equal to the case_value, it executes the else-statements in the ELSE clause if the ELSE clause is available.

When the ELSE clause does not exist and the CASE cannot find any when_value equal to the case_value, it issues an error:

Case not found for CASE statement
Note that the case_value can be a literal value or an expression. 
The statements can be one or more SQL statements, and cannot have zero statement.

To avoid the error when the
 case_value does not equal any when_value, you can use an empty BEGIN END block in the ELSE clause as follows:

CASE case_value
    WHEN when_value1 THEN ...
    WHEN when_value2 THEN ...
    ELSE 
        BEGIN
        END;
END CASE;
The simple CASE statement tests for equality ( =), you cannot use it to test equality with NULL; because NULL = NULL returns FALSE.


Simple CASE statement example

The following stored procedure illustrates how to use the simple CASE statement:

DELIMITER $$

CREATE PROCEDURE GetCustomerShipping(
	IN  pCustomerNUmber INT, 
	OUT pShipping       VARCHAR(50)
)
BEGIN
    DECLARE customerCountry VARCHAR(100);

SELECT 
    country
INTO customerCountry FROM
    customers
WHERE
    customerNumber = pCustomerNUmber;

    CASE customerCountry
		WHEN  'USA' THEN
		   SET pShipping = '2-day Shipping';
		WHEN 'Canada' THEN
		   SET pShipping = '3-day Shipping';
		ELSE
		   SET pShipping = '5-day Shipping';
	END CASE;
END$$

DELIMITER ;
How it works.

The GetCustomerShipping() stored procedure accepts two parameters: pCustomerNumber as an IN parameter and pShipping as an OUT parameter.

In the stored procedure:

First, select the country of the customer from the customers table by the input customer number.

Second, use the simple CASE statement to determine the shipping time based on the country of the customer. 
If the customer locates in USA , the shipping time is 2-day shipping . 
If the customer locates in Canada , the shipping time is 3-day shipping . 
The customers from other countries have 5-day shipping .

The following flowchart demonstrates the logic of the
 CASE statement for determining the shipping time:


This statement calls the stored procedure and passes the customer number 112:

CALL GetCustomerShipping(112,@shipping);
The following statement returns the shipping time of the customer 112:

SELECT @shipping;
Here is the output:

+----------------+
| @shipping      |
+----------------+
| 2-day Shipping |
+----------------+
1 row in set (0.00 sec)

Searched CASE statement

The simple CASE statement only allows you to compare a value with a set of distinct values.

To perform more complex matches such as ranges, you use the searched CASE statement. 
The searched CASE statement is equivalent to the IF 
statement, however, it’s much more readable than the IF statement.

Here is the basic syntax of the searched CASE statement:

CASE
    WHEN search_condition1 THEN statements
    WHEN search_condition1 THEN statements
    ...
    [ELSE else-statements]
END CASE;
In this syntax, searched CASE evaluates each search_condition in the WHEN clause until it finds a condition that evaluates to TRUE , then it executes the corresponding THEN clause statements.

If no search_condition evaluates to TRUE, the CASE will execute else-statements in the ELSE clause if an ELSE clause is available.

Similar to the simple CASE statement, if you don’t specify an ELSE clause and no condition is TRUE, MySQL raises the same error:

Case not found for CASE statement
MySQL also does not allow you to have empty statements in the THEN or ELSE clause. 
If you don’t want to handle the logic in the ELSE clause while preventing MySQL from raising an error in case no search_condition is true, you can use an empty BEGIN END 
block in the ELSE clause.


Searched CASE statement example

The following example demonstrates how to use a searched CASE statement to find customer level SILVER , GOLD or PLATINUM based on customer’s credit limit.

DELIMITER $$

CREATE PROCEDURE GetDeliveryStatus(
	IN pOrderNumber INT,
    OUT pDeliveryStatus VARCHAR(100)
)
BEGIN
	DECLARE waitingDay INT DEFAULT 0;
    SELECT 
		DATEDIFF(requiredDate, shippedDate)
	INTO waitingDay
	FROM orders
    WHERE orderNumber = pOrderNumber;
    
    CASE 
		WHEN waitingDay = 0 THEN 
			SET pDeliveryStatus = 'On Time';
        WHEN waitingDay >= 1 AND waitingDay < 5 THEN
			SET pDeliveryStatus = 'Late';
		WHEN waitingDay >= 5 THEN
			SET pDeliveryStatus = 'Very Late';
		ELSE
			SET pDeliveryStatus = 'No Information';
	END CASE;	
END$$
DELIMITER ;
How it works.

The stored procedure GetDeliveryStatus() accepts an order number as an IN parameter and returns the delivery status as an OUT parameter.

First, calculate the number of days between the required date and shipped date.

Second, determine the delivery status based on the number of waiting days using the searched CASE statement:


This statement uses the stored procedure GetDeliveryStatus() to get the delivery status of the order 10100 :

CALL GetDeliveryStatus(10100,@delivery);
Here is the result:




CASE vs. 
IF


Both IF and CASE statements allow you to execute a block of code based on a specific condition. 
Choosing between IF or CASE sometimes is just a matter of personal preference. 
Here are some guidelines:


In this tutorial, you have learned how to use two forms of the MySQL CASE statements including simple CASE statement and searched CASE statement.


Summary: how to use MySQL LOOP statement to run a block of code repeatedly based on a condition.


Introduction to MySQL LOOP statement

The LOOP statement allows you to execute one or more statements repeatedly.

Here is the basic syntax of the LOOP statement:

[begin_label:] LOOP
    statement_list
END LOOP [end_label]

The LOOP can have optional labels at the beginning and end of the block.

The LOOP executes the statement_list repeatedly. 
The statement_list may have one or more statements, each terminated by a semicolon (;) statement delimiter.

Typically, you terminate the loop when a condition is satisfied by using the LEAVE statement.

This is the typical syntax of the LOOP statement used with LEAVE statement:

[label]: LOOP
    ...
    -- terminate the loop
    IF condition THEN
        LEAVE [label];
    END IF;
    ...
END LOOP;

The LEAVE statement immediately exits the loop. 
It works like the break statement in other programming languages like PHP, C/C++, and Java.

In addition to the LEAVE statement, you can use the ITERATE statement to skip the current loop iteration and start a new iteration. 
The ITERATE is similar to the continue statement in PHP, C/C++, and Java.


LOOP statement example

The following statement creates a stored procedure that uses a LOOP
loop statement:

DROP PROCEDURE LoopDemo;

DELIMITER $$
CREATE PROCEDURE LoopDemo()
BEGIN
	DECLARE x  INT;
	DECLARE str  VARCHAR(255);
        
	SET x = 1;
	SET str =  '';
        
	loop_label:  LOOP
		IF  x > 10 THEN 
			LEAVE  loop_label;
		END  IF;
            
		SET  x = x + 1;
		IF  (x mod 2) THEN
			ITERATE  loop_label;
		ELSE
			SET  str = CONCAT(str,x,',');
		END  IF;
	END LOOP;
	SELECT str;
END$$

DELIMITER ;
In this example:


The following statement calls the stored procedure:

CALL LoopDemo();
Here is the output:

+-------------+
| str         |
+-------------+
| 2,4,6,8,10, |
+-------------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.02 sec)
In this tutorial, you have learned how to use the MySQL LOOP statement to execute a block of code repeatedly based on a condition.


Summary: how to use the MySQL WHILE loop statement to execute one or more statements repeatedly as long as a condition is true.


Introduction to MySQL WHILE loop statement

The WHILE loop is a loop statement that executes a block of code repeatedly as long as a condition is true.

Here is the basic syntax of the WHILE statement:

[begin_label:] WHILE search_condition DO
    statement_list
END WHILE [end_label]

In this syntax:

First, specify a search condition after the WHILE keyword.

The WHILE checks the search_condition at the beginning of each iteration.

If the search_condition evaluates to TRUE, the WHILE executes the statement_list as long as the search_condition is TRUE.

The WHILE loop is called a pretest loop because it checks the search_condition before the statement_list executes.

Second, specify one or more statements that will execute between the DO and END WHILE keywords.

Third, specify optional labels for the WHILE statement at the beginning and end of the loop construct.

The following flowchart illustrates the MySQL WHILE loop statement:




WHILE loop statement example

First, create a table namedcalendars which stores dates and derived date information such as day, month, quarter, and year:

CREATE TABLE calendars(
    id INT AUTO_INCREMENT,
    fulldate DATE UNIQUE,
    day TINYINT NOT NULL,
    month TINYINT NOT NULL,
    quarter TINYINT NOT NULL,
    year INT NOT NULL,
    PRIMARY KEY(id)
);

Second, create a new stored procedure to insert a date into the calendars table:

DELIMITER $$

CREATE PROCEDURE InsertCalendar(dt DATE)
BEGIN
    INSERT INTO calendars(
        fulldate,
        day,
        month,
        quarter,
        year
    )
    VALUES(
        dt, 
        EXTRACT(DAY FROM dt),
        EXTRACT(MONTH FROM dt),
        EXTRACT(QUARTER FROM dt),
        EXTRACT(YEAR FROM dt)
    );
END$$

DELIMITER ;

Third, create a new stored procedure LoadCalendars() that loads a number of days starting from a start date into the calendars table.

DELIMITER $$

CREATE PROCEDURE LoadCalendars(
    startDate DATE, 
    day INT
)
BEGIN
    
    DECLARE counter INT DEFAULT 1;
    DECLARE dt DATE DEFAULT startDate;

    WHILE counter <= day DO
        CALL InsertCalendar(dt);
        SET counter = counter + 1;
        SET dt = DATE_ADD(dt,INTERVAL 1 day);
    END WHILE;

END$$

DELIMITER ;

The stored procedure LoadCalendars() accepts two arguments:


In the LoadCalendars() stored procedure:

First, declare a counter and dt variables for keeping immediate values. 
The default values of counter and dt are 1 and startDate respectively.

Then, check if the counter is less than or equal day, if yes:


The WHILE loop repeatedly inserts dates into the calendars table until the counter is equal to day.

The following statement calls the stored procedure LoadCalendars() to load 31 days into the calendars table starting from January 1st 2019.

CALL LoadCalendars('2019-01-01',31);



In this tutorial, you have learned how to use the MySQL WHILE loop to execute one or more statements repeatedly as long as a condition is true.


Summary: how to use the MySQL REPEAT statement to execute one or more statements until a search condition is true.

The REPEAT statement executes one or more statements until a search condition is true.

Here is the basic syntax of the REPEAT loop statement:

[begin_label:] REPEAT
    statement
UNTIL search_condition
END REPEAT [end_label]

The REPEAT executes the statement until the search_condition evaluates to true.

The REPEAT checks the search_condition after the execution of statement, therefore, the statement always executes at least once. 
This is why the REPEAT is also known as a post-test loop.

The REPEAT statement can have labels at the beginning and at the end. 
These labels are optional.

The following flowchart illustrates the REPEAT loop:




REPEAT loop example

This statement creates a stored procedure called RepeatDemo
 that uses the REPEAT statement to concatenate numbers from 1 to 9:

DELIMITER $$

CREATE PROCEDURE RepeatDemo()
BEGIN
    DECLARE counter INT DEFAULT 1;
    DECLARE result VARCHAR(100) DEFAULT '';
    
    REPEAT
        SET result = CONCAT(result,counter,',');
        SET counter = counter + 1;
    UNTIL counter >= 10
    END REPEAT;
    
    -- display result
    SELECT result;
END$$

DELIMITER ;

In this stored procedure:

First, declare two variables counter and result and set their initial values to 1 and blank.

The counter variable is used for counting from 1 to 9 in the loop. 
And the result variable is used for storing the concatenated string after each loop iteration.

Second, append counter value to the result variable using the CONCAT() function until the counter is greater than or equal to 10.

The following statement calls the RepeatDemo() stored procedure:

CALL RepeatDemo();

Here is the output:

+--------------------+
| result             |
+--------------------+
| 1,2,3,4,5,6,7,8,9, |
+--------------------+
1 row in set (0.02 sec)

Query OK, 0 rows affected (0.02 sec)

how to use the MySQL REPEAT statement to execute one or more statement until a search condition is true.


Summary: how to the MySQL LEAVE statement to exit a stored program or terminate a loop.


Introduction to MySQL LEAVE statement

The LEAVE statement exits the flow control that has a given label.

The following shows the basic syntax of the LEAVE statement:

LEAVE label;

In this syntax, you specify the label of the block that you want to exit after the LEAVE keyword.


Using the LEAVE statement to exit a stored procedure

If the label is the outermost of the stored procedure
 or function block, LEAVE terminates the stored procedure or function.

The following statement shows how to use the LEAVE statement to exit a stored procedure:

CREATE PROCEDURE sp_name()
sp: BEGIN
    IF condition THEN
        LEAVE sp;
    END IF;
    -- other statement
END$$

For example, this statement creates a new stored procedure that checks the credit of a given customer in the customers table from the sample database:



DELIMITER $$

CREATE PROCEDURE CheckCredit(
    inCustomerNumber int
)
sp: BEGIN
    
    DECLARE customerCount INT;

    -- check if the customer exists
    SELECT 
        COUNT(*)
    INTO customerCount 
    FROM
        customers
    WHERE
        customerNumber = inCustomerNumber;
    
    -- if the customer does not exist, terminate
    -- the stored procedure
    IF customerCount = 0 THEN
        LEAVE sp;
    END IF;
    
    -- other logic
    -- ...
END$$

DELIMITER ;


Using LEAVE statement in loops

The LEAVE statement allows you to terminate a loop. 
The general syntax for the LEAVE statement when using in the LOOP, REPEAT and WHILE statements.

Using LEAVE with the LOOP statement:

[label]: LOOP
    IF condition THEN
        LEAVE [label];
    END IF;
    -- statements
END LOOP [label];

Using LEAVE with the REPEAT statement:

[label:] REPEAT
    IF condition THEN
        LEAVE [label];
    END IF;
    -- statements
UNTIL search_condition
END REPEAT [label];

Using LEAVE with the WHILE statement:

[label:] WHILE search_condition DO
    IF condition THEN
        LEAVE [label];
    END IF;
    -- statements
END WHILE [label];

The LEAVE causes the current loop specified by the label to be terminated. 
If a loop is enclosed within another loop, you can break out of both loops with a single LEAVE statement.


Using LEAVE statement in a loop example

The following stored procedure generates a string of integer with the number from 1 to a random number between 4 and 10:

DELIMITER $$

CREATE PROCEDURE LeaveDemo(OUT result VARCHAR(100))
BEGIN
    DECLARE counter INT DEFAULT 1;
    DECLARE times INT;
    -- generate a random integer between 4 and 10
    SET times  = FLOOR(RAND()*(10-4+1)+4);
    SET result = '';
    disp: LOOP
        -- concatenate counters into the result
        SET result = concat(result,counter,',');
        
        -- exit the loop if counter equals times
        IF counter = times THEN
            LEAVE disp; 
        END IF;
        SET counter = counter + 1;
    END LOOP;
END$$

DELIMITER ;

This statement calls the LeaveDemo procedure:

CALL LeaveDemo(@result);
SELECT @result;

Here is one of the outputs:

+------------------+
| @result          |
+------------------+
| 1,2,3,4,5,6,7,8, |
+------------------+
1 row in set (0.00 sec)

In this tutorial, you have learned how to the MySQL LEAVE statement to exit a stored program or terminate a loop.


Summary: how to use MySQL cursor in stored procedures to iterate through a result set returned by a SELECT statement.


Introduction to MySQL cursor

To handle a result set inside a stored procedure, you use a cursor. 
A cursor allows you to iterate a set of rows returned by a query and process each row individually.

MySQL cursor is read-only, non-scrollable and asensitive.


You can use MySQL cursors in stored procedures, stored functions, and triggers.


Working with MySQL cursor

First, declare a cursor by using the DECLARE statement:

DECLARE cursor_name CURSOR FOR SELECT_statement;
The cursor declaration must be after any variable declaration. 
If you declare a cursor before the variable declarations, MySQL will issue an error. 
A cursor must always associate with a SELECT statement.

Next, open the cursor by using the OPEN statement. 
The OPEN statement initializes the result set for the cursor, therefore, you must call the OPEN statement before fetching rows from the result set.

OPEN cursor_name;
Then, use the FETCH statement to retrieve the next row pointed by the cursor and move the cursor to the next row in the result set.

FETCH cursor_name INTO variables list;
After that, check if there is any row available before fetching it.

Finally, deactivate the cursor and release the memory associated with it
 using the CLOSE statement:

CLOSE cursor_name;
It is a good practice to always close a cursor when it is no longer used.

When working with MySQL cursor, you must also declare a NOT FOUND handler to handle the situation when the cursor could not find any row.

Because each time you call the FETCH statement, the cursor attempts to read the next row in the result set. 
When the cursor reaches the end of the result set, it will not be able to get the data, and a condition is raised. 
The handler is used to handle this condition.

To declare a NOT FOUND handler, you use the following syntax:

DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
The
finished is a variable to indicate that the cursor has reached the end of the result set. 
Notice that the handler declaration must appear after variable and cursor declaration inside the stored procedures.

The following diagram illustrates how MySQL cursor works.




Cursor Example

We’ll develop a stored procedure that creates an email list of all employees in the employees table in the
sample database.



First, declare some variables, a cursor for looping over the emails of employees, and a NOT FOUND handler:

	DECLARE finished INTEGER DEFAULT 0;
	DECLARE emailAddress varchar(100) DEFAULT "";

	-- declare cursor for employee email
	DEClARE curEmail 
		CURSOR FOR 
			SELECT email FROM employees;

	-- declare NOT FOUND handler
	DECLARE CONTINUE HANDLER 
        FOR NOT FOUND SET finished = 1;

Next, open the cursor by using the OPEN statement:

OPEN curEmail;
Then, iterate the email list, and concatenate all emails where each email is separated by a semicolon(;):

       getEmail: LOOP
		FETCH curEmail INTO emailAddress;
		IF finished = 1 THEN 
			LEAVE getEmail;
		END IF;
		-- build email list
		SET emailList = CONCAT(emailAddress,";",emailList);
	END LOOP getEmail;
After that, inside the loop, we used the finished variable to check if there is an email in the list to terminate the loop.

Finally, close the cursor using the CLOSE statement:

CLOSE email_cursor;
The createEmailList stored procedure is as follows:

DELIMITER $$
CREATE PROCEDURE createEmailList (
	INOUT emailList varchar(4000)
)
BEGIN
	DECLARE finished INTEGER DEFAULT 0;
	DECLARE emailAddress varchar(100) DEFAULT "";

	-- declare cursor for employee email
	DEClARE curEmail 
		CURSOR FOR 
			SELECT email FROM employees;

	-- declare NOT FOUND handler
	DECLARE CONTINUE HANDLER 
        FOR NOT FOUND SET finished = 1;

	OPEN curEmail;

	getEmail: LOOP
		FETCH curEmail INTO emailAddress;
		IF finished = 1 THEN 
			LEAVE getEmail;
		END IF;
		-- build email list
		SET emailList = CONCAT(emailAddress,";",emailList);
	END LOOP getEmail;
	CLOSE curEmail;

END$$
DELIMITER ;
You can test the createEmailList stored procedure using the following script:

SET @emailList = ""; 
CALL createEmailList(@emailList); 
SELECT @emailList;
In this tutorial, we have shown you how to use MySQL cursor to iterate a result set and process each row accordingly.


Summary: how to use MySQL handler to handle errors encountered in stored procedures.

When an error occurs inside a stored procedure, it is important to handle it appropriately, such as continuing or exiting the current code block’s execution, and issuing a meaningful error message.

MySQL provides an easy way to define handlers that handle from general conditions such as warnings or exceptions to specific conditions e.g., specific error codes.


Declaring a handler

To declare a handler, you use the
 DECLARE HANDLER statement as follows:

DECLARE action HANDLER FOR condition_value statement;
If a condition whose value matches the
 condition_value , MySQL will execute the statement and continue or exit the current code block based on the action .


The action accepts one of the following values:


The
 condition_value specifies a particular condition or a class of conditions that activate the handler. 
The
 condition_value accepts one of the following values:


The statement could be a simple statement or a compound statement enclosing by the BEGIN and END keywords.


error handling examples

Let’s take some examples of declaring handlers.

The following handler set the value of the
 hasError variable to 1 and continue the execution if an SQLEXCEPTION occurs

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION 
SET hasError = 1;
The following handler rolls back the previous operations, issues an error message, and exit the current code block in case an error occurs. 
If you declare it inside the BEGIN END block of a stored procedure, it will terminate the stored procedure immediately.

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
    ROLLBACK;
    SELECT 'An error has occurred, operation rollbacked and the stored procedure was terminated';
END;
The following handler sets the value of the
 RowNotFound variable to 1 and continues execution if there is no more row to fetch in case of a cursor or SELECT INTO statement:

DECLARE CONTINUE HANDLER FOR NOT FOUND 
SET RowNotFound = 1;
If a duplicate key error occurs, the following handler issues an error message and continues execution.

DECLARE CONTINUE HANDLER FOR 1062
SELECT 'Error, duplicate key occurred';

handler example in stored procedures

First, create a new table named SupplierProductsfor the demonstration:

CREATE TABLE SupplierProducts (
    supplierId INT,
    productId INT,
    PRIMARY KEY (supplierId , productId)
);
The table SupplierProducts stores the relationships between the table suppliers and products. 
Each supplier may provide many products and each product can be provided by many suppliers. 
For the sake of simplicity, we don’t create Products and Suppliers tables, as well as the foreign keys in the
 SupplierProducts table.

Second, create a stored procedure that inserts product id and supplier id into the SupplierProducts table:

CREATE PROCEDURE InsertSupplierProduct(
    IN inSupplierId INT, 
    IN inProductId INT
)
BEGIN
    -- exit if the duplicate key occurs
    DECLARE EXIT HANDLER FOR 1062
    BEGIN
 	SELECT CONCAT('Duplicate key (',inSupplierId,',',inProductId,') occurred') AS message;
    END;
    
    -- insert a new row into the SupplierProducts
    INSERT INTO SupplierProducts(supplierId,productId)
    VALUES(inSupplierId,inProductId);
    
    -- return the products supplied by the supplier id
    SELECT COUNT(*) 
    FROM SupplierProducts
    WHERE supplierId = inSupplierId;
    
END$$

DELIMITER ;
How it works.

The following exit handler terminates the stored procedure whenever a duplicate key occurs (with code 1062). 
In addition, it returns an error message.

DECLARE EXIT HANDLER FOR 1062
BEGIN
    SELECT CONCAT('Duplicate key (',supplierId,',',productId,') occurred') AS message;
END;
This statement inserts a row into the SupplierProducts table. 
If a duplicate key occurs, the code in the handler section will execute.

INSERT INTO SupplierProducts(supplierId,productId) 
VALUES(supplierId,productId);
Third, call the InsertSupplierProduct() to insert some rows into the SupplierProducts table:

CALL InsertSupplierProduct(1,1);
CALL InsertSupplierProduct(1,2);
CALL InsertSupplierProduct(1,3);
Fourth, attempt to insert a row whose values already exist in the SupplierProducts table:

CALL InsertSupplierProduct(1,3);
Here is the error message:

+------------------------------+
| message                      |
+------------------------------+
| Duplicate key (1,3) occurred |
+------------------------------+
1 row in set (0.01 sec)
Because the handler is an EXIT handler, the last statement does not execute:

SELECT COUNT(*) 
FROM SupplierProducts
WHERE supplierId = inSupplierId;
If
 you change the EXIT in the handler declaration to CONTINUE , you will also get the number of products provided by the supplier:

DROP PROCEDURE IF EXISTS InsertSupplierProduct;

DELIMITER $$

CREATE PROCEDURE InsertSupplierProduct(
    IN inSupplierId INT, 
    IN inProductId INT
)
BEGIN
    -- exit if the duplicate key occurs
    DECLARE CONTINUE HANDLER FOR 1062
    BEGIN
	SELECT CONCAT('Duplicate key (',inSupplierId,',',inProductId,') occurred') AS message;
    END;
    
    -- insert a new row into the SupplierProducts
    INSERT INTO SupplierProducts(supplierId,productId)
    VALUES(inSupplierId,inProductId);
    
    -- return the products supplied by the supplier id
    SELECT COUNT(*) 
    FROM SupplierProducts
    WHERE supplierId = inSupplierId;
    
END$$

DELIMITER ;
Finally, call the stored procedure again to see the effect of the CONTINUE handler:

CALL InsertSupplierProduct(1,3);
Here is the output:

+----------+
| COUNT(*) |
+----------+
|        3 |
+----------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.02 sec)

handler precedence

In case you have multiple handlers that handle the same error, MySQL will call the most specific handler to handle the error first based on the following rules:


Based on the handler precedence rules, 
MySQL error code handler, SQLSTATE handler and SQLEXCEPTION takes the first, second and third precedence.

Suppose that we have three handlers in the handlers in the stored procedure insert_article_tags_3 :

DROP PROCEDURE IF EXISTS InsertSupplierProduct;

DELIMITER $$

CREATE PROCEDURE InsertSupplierProduct(
    IN inSupplierId INT, 
    IN inProductId INT
)
BEGIN
    -- exit if the duplicate key occurs
    DECLARE EXIT HANDLER FOR 1062 SELECT 'Duplicate keys error encountered' Message; 
    DECLARE EXIT HANDLER FOR SQLEXCEPTION SELECT 'SQLException encountered' Message; 
    DECLARE EXIT HANDLER FOR SQLSTATE '23000' SELECT 'SQLSTATE 23000' ErrorCode;
    
    -- insert a new row into the SupplierProducts
    INSERT INTO SupplierProducts(supplierId,productId)
    VALUES(inSupplierId,inProductId);
    
    -- return the products supplied by the supplier id
    SELECT COUNT(*) 
    FROM SupplierProducts
    WHERE supplierId = inSupplierId;
    
END$$

DELIMITER ;
Call the stored procedure to insert a duplicate key:

CALL InsertSupplierProduct(1,3);
Here is the output:

+----------------------------------+
| Message                          |
+----------------------------------+
| Duplicate keys error encountered |
+----------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)
As you see the MySQL error code handler is called.


Using a named error condition

Let’s start with an error handler declaration.

DELIMITER $$

CREATE PROCEDURE TestProc()
BEGIN

    DECLARE EXIT HANDLER FOR 1146 
    SELECT 'Please create table abc first' Message; 
        
    SELECT * FROM abc;
END$$

DELIMITER ;
What does the number 1146 really mean? Imagine you have stored procedures polluted with these numbers all over places; it will be difficult to understand and maintain the code.

Fortunately, MySQL provides you with the DECLARE CONDITION statement that declares a named error condition, which associates with a condition.

Here is the syntax of the DECLARE CONDITION statement:

DECLARE condition_name CONDITION FOR condition_value;
The condition_value 
can be a MySQL error code such as 1146
or a SQLSTATE value. 
The condition_value is represented by the condition_name .

After the declaration, you can refer to condition_name
instead of condition_value .

So you can rewrite the code above as follows:

DROP PROCEDURE IF EXISTS TestProc;

DELIMITER $$

CREATE PROCEDURE TestProc()
BEGIN
    DECLARE TableNotFound CONDITION for 1146 ; 

    DECLARE EXIT HANDLER FOR TableNotFound 
	SELECT 'Please create table abc first' Message; 
    SELECT * FROM abc;
END$$

DELIMITER ;
As you can see, the code is more obviously and readable than the previous one. 
Notice that the condition declaration must appear before handler or cursor declarations.

In this tutorial, you have learned how to use MySQL handlers to handle exception or errors occurred in stored procedures.


Summary: how to use SIGNAL 
and RESIGNAL
statements to raise error conditions inside stored procedures.


SIGNAL statement

You use the SIGNAL
statement to return an error or warning condition to the caller from a stored program e.g., stored procedure, stored function,
trigger or event. 
The SIGNAL 
statement provides you with control over which information for returning such as value and messageSQLSTATE.

The following illustrates syntax of the SIGNAL statement:

SIGNAL SQLSTATE | condition_name;
SET condition_information_item_name_1 = value_1,
    condition_information_item_name_1 = value_2, etc;
Following the SIGNAL keyword is a
SQLSTATE value or a condition name declared by the
 DECLARE CONDITION statement. 
Notice that the SIGNAL statement must always specify a
SQLSTATE value or a named condition that defined with an
 SQLSTATE value.

To provide the caller with information, you use the SET clause. 
If you want to return multiple condition information item names with values, you need to separate each name/value pair by a comma.

The
 condition_information_item_name can be MESSAGE_TEXT, MYSQL_ERRORNO, CURSOR_NAME , etc.

The following stored procedure adds an order line item into an existing sales order. 
It issues an error message if the order number does not exist.

DELIMITER $$

CREATE PROCEDURE AddOrderItem(
		         in orderNo int,
			 in productCode varchar(45),
			 in qty int, 
                         in price double, 
                         in lineNo int )
BEGIN
	DECLARE C INT;

	SELECT COUNT(orderNumber) INTO C
	FROM orders 
	WHERE orderNumber = orderNo;

	-- check if orderNumber exists
	IF(C != 1) THEN 
		SIGNAL SQLSTATE '45000'
			SET MESSAGE_TEXT = 'Order No not found in orders table';
	END IF;
	-- more code below
	-- ...
END
First, it counts the orders with the input order number that we pass to the stored procedure.

Second, if the number of order is not 1, it raises an error with
 SQLSTATE 45000 along with an error message saying that order number does not exist in the orders table.

Notice that 45000 is a generic SQLSTATE value that illustrates an unhandled user-defined exception.

If we call the stored procedure
 AddOrderItem() and pass a nonexistent order number, we will get an error message.

CALL AddOrderItem(10,'S10_1678',1,95.7,1);



RESIGNAL statement

Besides the SIGNAL 
statement, MySQL also provides the RESIGNAL 
statement used to raise a warning or error condition.

The RESIGNAL 
statement is similar to SIGNAL 
statement in term of functionality and syntax, except that:


If you use the RESIGNAL
statement alone, all attributes are the same as the ones passed to the condition handler.

The following stored procedure changes the error message before issuing it to the caller.

DELIMITER $$

CREATE PROCEDURE Divide(IN numerator INT, IN denominator INT, OUT result double)
BEGIN
	DECLARE division_by_zero CONDITION FOR SQLSTATE '22012';

	DECLARE CONTINUE HANDLER FOR division_by_zero 
	RESIGNAL SET MESSAGE_TEXT = 'Division by zero / Denominator cannot be zero';
	-- 
	IF denominator = 0 THEN
		SIGNAL division_by_zero;
	ELSE
		SET result := numerator / denominator;
	END IF;
END
Let’s call the
 Divide() stored procedure.

CALL Divide(10,0,@result);


In this tutorial, we have shown you how to raise error conditions inside stored programs
using SIGNAL 
and
 RESIGNAL statements.



Summary: how to develop stored procedures that return multiple values.

MySQL stored function returns only one value. 
To develop stored programs that return multiple values, you need to use stored procedures with INOUT or OUT parameters.

If you are not familiar with INOUT or OUT parameters, check it out the stored procedure’s parameters tutorial for the detailed information.


Stored procedures that return multiple values example

Let’s take a look at the orders table in the sample database.



The following stored procedure accepts
customer number and returns the total number of orders that were shipped, canceled, resolved, and disputed.

DELIMITER $$

CREATE PROCEDURE get_order_by_cust(
	IN cust_no INT,
	OUT shipped INT,
	OUT canceled INT,
	OUT resolved INT,
	OUT disputed INT)
BEGIN
		-- shipped
		SELECT
            count(*) INTO shipped
        FROM
            orders
        WHERE
            customerNumber = cust_no
                AND status = 'Shipped';

		-- canceled
		SELECT
            count(*) INTO canceled
        FROM
            orders
        WHERE
            customerNumber = cust_no
                AND status = 'Canceled';

		-- resolved
		SELECT
            count(*) INTO resolved
        FROM
            orders
        WHERE
            customerNumber = cust_no
                AND status = 'Resolved';

		-- disputed
		SELECT
            count(*) INTO disputed
        FROM
            orders
        WHERE
            customerNumber = cust_no
                AND status = 'Disputed';

END
In addition to the IN parameter, the stored procedure takes four additional OUT parameters: shipped, canceled, resolved, and disputed. 
Inside the stored procedure, you use a SELECT statement with the COUNT function to get the corresponding total of orders based on the order’s status and assign it to the respective parameter.

To use the get_order_by_cust stored procedure, you pass customer number and four user-defined variables to get the out values.

After executing the stored procedure, you use the SELECT statement to output the variable values.

CALL get_order_by_cust(141,@shipped,@canceled,@resolved,@disputed);
SELECT @shipped,@canceled,@resolved,@disputed;



Calling stored procedures that return multiple values from PHP

The following code snippet shows you how to call the stored procedure that returns multiple values from PHP.

<?php
/**
 * Call stored procedure that return multiple values
 * @param $customerNumber
 */
function call_sp($customerNumber)
{
    try {
        $pdo = new PDO("mysql:host=localhost;dbname=classicmodels", 'root', '');

        // execute the stored procedure
        $sql = 'CALL get_order_by_cust(:no,@shipped,@canceled,@resolved,@disputed)';
        $stmt = $pdo->prepare($sql);

        $stmt->bindParam(':no', $customerNumber, PDO::PARAM_INT);
        $stmt->execute();
        $stmt->closeCursor();

        // execute the second query to get values from OUT parameter
        $r = $pdo->query("SELECT @shipped,@canceled,@resolved,@disputed")
                  ->fetch(PDO::FETCH_ASSOC);
        if ($r) {
            printf('Shipped: %d, Canceled: %d, Resolved: %d, Disputed: %d',
                $r['@shipped'],
                $r['@canceled'],
                $r['@resolved'],
                $r['@disputed']);
        }
    } catch (PDOException $pe) {
        die("Error occurred:" . 
$pe->getMessage());
    }
}

call_sp(141);
The user-defined variables, which are
preceded
by the @ sign, are associated with the database connection, therefore, they are available for access between the calls.

In this tutorial, we have shown you how to develop a stored procedure that returns multiple values and how to call it from PHP.


Summary: how to create stored functions using the CREATE FUNCTION statement.

A stored function is a special kind stored program that returns a single value. 
Typically, you use stored functions to encapsulate common formulas or business rules that are reusable among SQL statements or stored programs.

Different from a stored procedure, you can use a stored function in SQL statements wherever an expression is used. 
This helps improve the readability and maintainability of the procedural code.

To create a stored function, you use the CREATE FUNCTION statement.


CREATE FUNCTION syntax

The following illustrates the basic syntax for creating a new stored function:

DELIMITER $$

CREATE FUNCTION function_name(
    param1,
    param2,…
)
RETURNS datatype
[NOT] DETERMINISTIC
BEGIN
 -- statements
END $$

DELIMITER ;
In this syntax:

First, specify the name of the stored function that you want to create after CREATE FUNCTION 
keywords.

Second, list all parameters of the stored function inside the parentheses followed by the function name. 
By default, all parameters are the IN parameters. 
You cannot specify IN , OUT or INOUT modifiers to parameters

Third, specify the data type of the return value in the RETURNS statement, which can be any valid MySQL data types.

Fourth, specify if a function is deterministic or not using the DETERMINISTIC keyword.

A deterministic function always returns the same result for the same input parameters whereas a non-deterministic function returns different results for the same input parameters.

If you don’t use DETERMINISTIC or NOT DETERMINISTIC, MySQL uses the NOT DETERMINISTIC option by default.

Fifth, write the code in the body of the stored function in the BEGIN END block. 
Inside the body section, you need to specify at least one RETURN statement.
The RETURN statement returns a value to the calling programs. 
Whenever the RETURN statement is reached, the execution of the stored function is terminated immediately.


CREATE FUNCTION example

Let’s take the example of creating a stored function. 
We will use the customers table in the sample database for the demonstration.



The following CREATE FUNCTION statement creates a function that returns the customer level based on credit:

DELIMITER $$

CREATE FUNCTION CustomerLevel(
	credit DECIMAL(10,2)
) 
RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
    DECLARE customerLevel VARCHAR(20);

    IF credit > 50000 THEN
		SET customerLevel = 'PLATINUM';
    ELSEIF (credit >= 50000 AND 
			credit <= 10000) THEN
        SET customerLevel = 'GOLD';
    ELSEIF credit < 10000 THEN
        SET customerLevel = 'SILVER';
    END IF;
	-- return the customer level
	RETURN (customerLevel);
END$$
DELIMITER ;
Once the function is created, you can view it in MySQL Workbench under the Functions section:



Or you can view all stored functions in the current classicmodels database by using the SHOW FUNCTION STATUS as follows:

SHOW FUNCTION STATUS 
WHERE db = 'classicmodels';



Calling a stored function in an SQL statement

The following statement uses the CustomerLevel stored function:

SELECT 
    customerName, 
    CustomerLevel(creditLimit)
FROM
    customers
ORDER BY 
    customerName;



Calling a stored function in a stored procedure

The following statement creates a new stored procedure that calls the CustomerLevel()
stored function:

DELIMITER $$

CREATE PROCEDURE GetCustomerLevel(
    IN  customerNo INT,  
    OUT customerLevel VARCHAR(20)
)
BEGIN

	DECLARE credit DEC(10,2) DEFAULT 0;
    
    -- get credit limit of a customer
    SELECT 
		creditLimit 
	INTO credit
    FROM customers
    WHERE 
		customerNumber = customerNo;
    
    -- call the function 
    SET customerLevel = CustomerLevel(credit);
END$$

DELIMITER ;
The following illustrates how to call the GetCustomerLevel()
stored procedure:

CALL GetCustomerLevel(-131,@customerLevel);
SELECT @customerLevel;
It’s important to notice that if a stored function contains SQL statements that query data from tables, then you should not use it in other SQL statements; otherwise, the stored function will slow down the speed of the query.

In this tutorial, you have learned how to create a stored function to encapsulate the common formula or business rules.


Summary: how to use the MySQL DROP FUNCTION statement to drop a stored function.


Introduction to MySQL DROP FUNCTION statement

The DROP FUNCTION statement drops a stored function. 
Here is the syntax of the DROP FUNCTION statement:

DROP FUNCTION [IF EXISTS] function_name;

In this syntax, you specify the name of the stored function that you want to drop after the DROP FUNCTION keywords.

The IF EXISTS option allows you to conditionally drop a stored function if it exists. 
It prevents an error from arising if the function does not exist.


DROP FUNCTION example

We’ll use the orders table in the sample database for the demonstration.



First, create a new function called OrderLeadTime that calculates the number of days between ordered date and required date:

DELIMITER $$

CREATE FUNCTION OrderLeadTime (
    orderDate DATE,
    requiredDate DATE
) 
RETURNS INT
DETERMINISTIC
BEGIN
    RETURN requiredDate - orderDate;
END$$

DELIMITER ;

Second, use the DROP FUNCTION statement to drop the function OrderLeadTime:

DROP FUNCTION OrderLeadTime;

Third, use the DROP FUNCTION to drop a non-existing function:

DROP FUNCTION IF EXISTS NonExistingFunction;

MySQL issued a warning:

0 row(s) affected, 1 warning(s): 1305 FUNCTION classicmodels.NonExistingFunction does not exist

If you want to view the warning in detail, use the SHOW WARNINGS statement:

SHOW WARNINGS;



how to use the DROP FUNCTION statement to drop a stored function.


Summary: how to show stored functions from databases by using the SHOW FUNCTION STATUS or querying the data dictionary.


Listing stored functions using SHOW FUNCTION STATUS statement

The SHOW FUNCTION STATUS is like the SHOW PROCEDURE STATUS but for the stored functions.

Here is the basic syntax of the SHOW FUNCTION STATUS statement:

SHOW FUNCTION STATUS 
[LIKE 'pattern' | WHERE search_condition];
The SHOW FUNCTION STATUS statement returns all characteristics of stored functions. 
The following statement shows all stored functions in the current MySQL server:

SHOW FUNCTION STATUS;
Note that the SHOW FUNCTION STATUS only shows the function that you have a privilege to access.

If you just want to show stored functions in a particular database, you can use a WHERE clause in the
 SHOW FUNCTION STATUS as shown in the following statement:

SHOW FUNCTION STATUS 
WHERE search_condition;
For example, this statement shows all stored functions in the sample database classicmodels:

SHOW FUNCTION STATUS 
WHERE db = 'classicmodels';
If you want to find the stored functions whose names contain a specific word, you can use the LIKE clause:

SHOW FUNCTION STATUS 
LIKE '%pattern%';
The following statement shows all stored functions whose names contain the word Customer:

SHOW FUNCTION STATUS LIKE '%Customer%';

Listing stored functions using the data dictionary

MySQL data dictionary has a routines table that stores information about the stored functions of all databases in the current MySQL server.

This query finds all stored functions in a particular database:

SELECT 
    routine_name
FROM
    information_schema.routines
WHERE
    routine_type = 'FUNCTION'
        AND routine_schema = '<database_name>';
For example, the following statement returns all stored functions in the classicmodels database:

SELECT 
    routine_name
FROM
    information_schema.routines
WHERE
    routine_type = 'FUNCTION'
        AND routine_schema = 'classicmodels';

Showing stored functions using MySQL Workbench

If you use MySQL Workbench, you can view all stored functions from a database.

Step 1. 
Connect to the database that you want to show the stored functions.

Step 2. 
Open the Functions menu, you will see a list of functions which belong to the database.



In this tutorial, you have learned how to show stored functions in a database by using the SHOW FUNCTION STATUS statement and querying from the data dictionary.